﻿package com.dykj.platform.framework.dao.hibernate;

import java.io.Serializable;
import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.HibernateTemplate;
import com.dykj.platform.framework.common.paging.Page;
import com.dykj.platform.framework.dao.AbstractDBAccess;
import com.dykj.platform.framework.exception.DatabaseException;
import com.dykj.platform.framework.utils.SQLUtils;

/**
 * @Title: DYKJ platform
 * @Description: Hibernate数据访问实现
 * @Copyright: Copyright (c) 2008
 * @Company: DYKJ
 * @author jit@dykj.cn
 * @version 1.0
 */
public class HibernateDBAccess extends AbstractDBAccess implements
		IHibernateDBAccess {

	private static final long serialVersionUID = 335750856866590394L;

	protected Transaction trans;

	public HibernateDBAccess() {
		trans = null;
	}

	/**
	 * 获取记录条数
	 */
	public int getCount(String queryString) throws DatabaseException {
		return getCount(queryString, null);
	}

	/**
	 * 获取记录条数
	 */
	public int getCount(String queryString, final List param)
			throws DatabaseException {
		int count = 0;
		final String countHQL = SQLUtils.getCountSQL(queryString);
		count = Integer.parseInt((String) this.getTemplate().execute(
				new HibernateCallback() {

					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						Query query = session.createQuery(countHQL);
						if (param != null) {
							for (int i = 0; i < param.size(); i++) {
								query.setParameter(i, param.get(i));
							}
						}
						return query.list().get(0).toString();
					}
				}));
		return count;
	}

	/**
	 * 获取对象
	 */
	public Object getObject(Class entityClass, Serializable id)
			throws DatabaseException {
		return getTemplate().get(entityClass, id);
	}

	public Object loadObject(Class entityClass, Serializable id)
			throws DatabaseException {
		return getTemplate().load(entityClass, id);
	}

	/**
	 * 分页查询
	 */
	public List pageQuery(final String queryString, final int firstResult,
			final int maxResults) throws DatabaseException {
		return (List) this.getTemplate().execute(new HibernateCallback() {

			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(queryString);
				if (firstResult >= 0) {
					query.setFirstResult(firstResult);
				}
				if (maxResults > 0) {
					query.setMaxResults(maxResults);
				}
				return query.list();
			}
		});
	}

	/**
	 * 分页查询
	 */
	public List pageQuery(Object entity, int firstResult, int maxResults)
			throws DatabaseException {
		return getTemplate().findByExample(entity, firstResult, maxResults);
	}

	/**
	 * 分页查询
	 */
	public List pageQuery(String queryString, Page page)
			throws DatabaseException {
		List list = null;
		if (page == null) {
			list = query(queryString);
		} else {
			int firstResult = (page.getCurrentPageNo() - 1)
					* page.getPageSize();
			int maxResults = page.getPageSize();
			list = pageQuery(queryString, firstResult, maxResults);
		}
		return list;
	}

	/**
	 * 分页查询
	 */
	public List pageQuery(final String queryString, final List param,
			final int firstResult, final int maxResults)
			throws DatabaseException {
		return (List) this.getTemplate().execute(new HibernateCallback() {

			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(queryString);
				if (firstResult >= 0) {
					query.setFirstResult(firstResult);
				}
				if (maxResults > 0) {
					query.setMaxResults(maxResults);
				}
				if (param != null) {
					for (int i = 0; i < param.size(); i++) {
						query.setParameter(i, param.get(i));
					}
				}
				return query.list();
			}
		});
	}

	/**
	 * 分页查询
	 */
	public List pageQuery(String queryString, List param, Page page)
			throws DatabaseException {
		List list = null;
		if (page == null) {
			list = query(queryString, param);
		} else {
			int firstResult = (page.getCurrentPageNo() - 1)
					* page.getPageSize();
			int maxResults = page.getPageSize();
			list = pageQuery(queryString, param, firstResult, maxResults);
		}
		return list;
	}

	/**
	 * 查询
	 */
	public List query(String queryString) throws DatabaseException {
		return getTemplate().find(queryString);
	}

	/**
	 * 查询
	 */
	public List query(String queryString, List param) throws DatabaseException {
		return getTemplate().find(queryString, param);
	}

	public List query(Object entity) throws DatabaseException {
		return getTemplate().findByExample(entity);
	}

	/**
	 * 保存
	 */
	public void save(Object entity) throws DatabaseException {
		try {
			getTemplate().save(entity);
		} catch (Exception e) {
			throw new DatabaseException(e);
		}
	}

	/**
	 * 保存或者更新
	 */
	public void saveOrUpdate(Object entity) throws DatabaseException {
		try {
			getTemplate().saveOrUpdate(entity);
		} catch (Exception e) {
			throw new DatabaseException(e);
		}
	}

	/**
	 * 更新
	 */
	public void update(Object entity) throws DatabaseException {
		try {
			getTemplate().update(entity);
		} catch (Exception e) {
			throw new DatabaseException(e);
		}
	}

	/**
	 * 删除
	 */
	public void delete(Object entity) throws DatabaseException {
		try {
			getTemplate().delete(entity);
		} catch (Exception e) {
			throw new DatabaseException(e);
		}
	}

	/**
	 * 删除
	 */
	public void delByKey(Class entityClass, Serializable key)
			throws DatabaseException {
		try {
			Object obj = loadObject(entityClass, key);
			if (obj != null) {
				getTemplate().delete(obj);
			}
		} catch (Exception e) {
			throw new DatabaseException(e);
		}
	}

	/**
	 * 获取 HibernateTemplate
	 * 
	 * @return
	 * @throws DatabaseException
	 */
	protected HibernateTemplate getTemplate() throws DatabaseException {
		if (template == null)
			throw new DatabaseException("DBAccess template should not be null!");
		else
			return (HibernateTemplate) template;
	}

	/**
	 * 获取 HibernateSession
	 */
	public Session getSession() throws DatabaseException {
		Session session = getSessionFactory().getCurrentSession();
		if (session == null || !session.isOpen())
			session = getSessionFactory().openSession();
		return session;
	}

	/**
	 * 获取 SessionFactory
	 * 
	 * @return
	 * @throws DatabaseException
	 */
	public SessionFactory getSessionFactory() throws DatabaseException {
		return getTemplate().getSessionFactory();
	}

	/**
	 * 获取 sequence
	 * 
	 * @param sequenceName
	 * @return
	 * @throws DatabaseException
	 */
	public String getSequence(String sequenceName) throws DatabaseException {
		String nextval = null;
		final String sequenceSQL = this.dialect
				.getSequenceNextValString(sequenceName);

		nextval = (String) this.getTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Object result = null;
				Query query = session.createSQLQuery(sequenceSQL);
				result = query.list().get(0);
				return result.toString();
			}
		});
		return nextval;
	}

	public int beginTransaction() throws DatabaseException {
		if (trans == null || !trans.isActive()) {
			try {
				trans = getSession().beginTransaction();
			} catch (Exception e) {
				throw new DatabaseException(e);
			}
			return 1;
		} else {
			return 0;
		}
	}

	public void commit(int flag) throws DatabaseException {
		if (flag == 1 && trans != null)
			try {
				trans.commit();
				trans = null;
			} catch (Exception e) {
				throw new DatabaseException(e);
			}
	}

	public void rollback() throws DatabaseException {
		if (trans != null)
			try {
				trans.rollback();
			} catch (Exception e) {
				throw new DatabaseException(e);
			}
	}

	public int execDML(String dml) throws DatabaseException {
		return execDML(dml, null);
	}

	public int execDML(String dml, List param) throws DatabaseException {
		int result = 0;
		try {
			Session session = getSession();
			Query query = session.createQuery(dml);
			int size = param != null ? param.size() : 0;
			for (int i = 0; i < size; i++)
				if (param.get(i) instanceof Date)
					query.setTime(i, (Date) param.get(i));
				else
					query.setParameter(i, param.get(i));

			result = query.executeUpdate();
		} catch (HibernateException e) {
			throw new DatabaseException(e);
		}
		return result;
	}

	public List callProcedure(String fullname, Object para_in[],
			int paraout_type[], boolean isTransection) throws DatabaseException {
		Session session = getSession();
		Connection con = null;
		CallableStatement proc = null;
		List returnObjs = new ArrayList();
		List list = null;
		try {
			con = session.connection();
			StringBuffer procedure_name = new StringBuffer("{call ");
			procedure_name.append(fullname);
			procedure_name.append("(");
			for (int i = 0; i < para_in.length + paraout_type.length; i++)
				if (i == (para_in.length + paraout_type.length) - 1)
					procedure_name.append("?");
				else
					procedure_name.append("?,");
			procedure_name.append(")}");
			proc = con.prepareCall(procedure_name.toString());
			for (int i = 1; i <= para_in.length; i++) {
				Object para_obj = para_in[i - 1];
				if (para_obj instanceof String)
					proc.setString(i, para_obj.toString());
				else if (para_obj instanceof BigDecimal)
					proc.setBigDecimal(i, (BigDecimal) para_obj);
				else if (para_obj instanceof Date)
					proc.setDate(i, (Date) para_obj);
				else
					throw new DatabaseException(
							"Procedure parameter type not understand!");
			}

			for (int i = para_in.length + 1; i <= para_in.length
					+ paraout_type.length; i++)
				proc.registerOutParameter(i, paraout_type[i - para_in.length
						- 1]);

			proc.execute();
			for (int i = 1; i <= paraout_type.length; i++)
				returnObjs.add(proc.getObject(i + para_in.length));

			proc.close();
			list = returnObjs;
		} catch (Exception ex) {
			throw new DatabaseException(ex);
		}
		return list;
	}

	public List queryNamed(String namedQueryString, List param)
			throws DatabaseException {
		List result = null;
		try {
			Session session = getSession();
			Query query = session.getNamedQuery(namedQueryString);
			int size = param != null ? param.size() : 0;
			for (int i = 0; i < size; i++)
				if (param.get(i) instanceof Date)
					query.setTime(i, (Date) param.get(i));
				else
					query.setParameter(i, param.get(i));

			result = query.list();
		} catch (HibernateException e) {
			throw new DatabaseException(e);
		}
		return result;
	}

	public List queryNamed(String namedQueryString) throws DatabaseException {
		return queryNamed(namedQueryString, null);
	}

	/**
	 * 获取 JdbcTemplate
	 * 
	 * @return
	 * @throws DatabaseException
	 */
	public JdbcTemplate getJdbcTemplate() throws DatabaseException {
		if (jdbcTemplate == null)
			throw new DatabaseException(
					"DBAccess JdbcTemplate should not be null!");
		else
			return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	/**
	 * JDBC分页查询
	 */
	public List pageJdbcQuery(String sql, int firstResult, int maxResults)
			throws DatabaseException {
		sql = sql.trim();
		StringBuffer pagingSelect = new StringBuffer(sql.length() + 100);
		pagingSelect
				.append("select * from ( select row_.*,rownum as rn from ( ");
		pagingSelect.append(sql);
		pagingSelect.append(" ) row_ where rownum <= ");
		pagingSelect.append(maxResults + firstResult - 1);
		pagingSelect.append(") where rn >= ");
		pagingSelect.append(firstResult);

		List list = getJdbcTemplate().queryForList(pagingSelect.toString());

		return list;
	}

	public List queryJDBC(String sql) throws DatabaseException {
		List list = getJdbcTemplate().queryForList(sql);
		return list;
	}

	public String execSQL(String sql) {
		String err = null;
		try {
			getJdbcTemplate().execute(sql);
			err = "0";
		} catch (Exception e) {
			err = e.getMessage();
			e.printStackTrace();
		}
		return err;
	}

	public int getJdbcCount(String sql) throws DatabaseException {

		String subSQL = sql.substring(sql.indexOf("from"));
		StringBuffer sqlBuffer = new StringBuffer("");
		sqlBuffer.append("select count(*) ");
		sqlBuffer.append(subSQL);
		int count = getJdbcTemplate().queryForInt(sqlBuffer.toString());
		return count;
	}

	/**
	 * 取得主键序列的实现
	 * 
	 * @param index
	 * @param table
	 * @return
	 * @throws DatabaseException
	 */
	public String getKeyIndex(String key, String table)
			throws DatabaseException {
		return getSequence("seq_" + table);
	}
}
